import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def hello():
    print('line operation')


def getAllMark():
    print('return all marks')

    sql = "select mileagemark_id,mileagemark_name,mileagemark.line_id,line_name,start_point_id," \
          "s1.`station_name` as start_station,end_point_id,s2.`station_name` as stop_station " \
          "from mileagemark left join line on mileagemark.line_id = line.line_id " \
          "LEFT JOIN `station`as s1 on mileagemark.`start_point_id` =s1.station_id " \
          "LEFT JOIN `station` as s2 on mileagemark.`end_point_id` =s2.station_id order by mileagemark_id"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)

    if temp == ():
        print("没有站点")
        return 0
    else:
        print("查询成功")
        return temp


# 获取线路经过的所有站点,id和名字
def getstations(station_id_list):
    if not station_id_list:
        return 0
    else:
        stationlist = []

        for station_id in station_id_list:
            sql = "select station_id,station_name from station where station_id='" + station_id + "'"
            print(sql)
            cursor.execute(sql)
            temp = cursor.fetchall()
            print(temp)
            stationlist.append({"station_id": temp[0][0], "station_name": temp[0][1]})
        print(stationlist)
        return stationlist


def newmilemark(milemark_id, milemark_name, line_id, start_point_id, end_point_id):
    print('insert a new milemark')

    sql = "insert into mileagemark values('" + milemark_id + "','" + milemark_name + "','" + line_id + "','" \
          + start_point_id + "','" + end_point_id + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getnewnumber():
    sql = "select max(station_id) from station"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str


def editmilemark(milemark_id, milemark_name, line_id, start_point_id, end_point_id):
    print('edit a milemark')

    sql = "update mileagemark set mileagemark_name='" + milemark_name + "',line_id='" + line_id + \
          "',start_point_id='" + start_point_id + "',end_point_id='" + end_point_id + "' where mileagemark_id=" + milemark_id
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchMark(word):
    sql = "select mileagemark_id,mileagemark_name,mileagemark.line_id,line_name,start_point_id," \
          "s1.`station_name` as start_station,end_point_id,s2.`station_name` as stop_station " \
          "from mileagemark left join line on mileagemark.line_id = line.line_id " \
          "LEFT JOIN `station`as s1 on mileagemark.`start_point_id` =s1.station_id " \
          "LEFT JOIN `station` as s2 on mileagemark.`end_point_id` =s2.station_id " \
          "where mileagemark.mileagemark_name like '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的站点")
        return 0
    else:
        print("搜索成功")
        return temp


def deleteMilemark(word):
    sql = "Delete FROM mileagemark WHERE mileagemark_id =" + str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getOptions():
    print('return all line')

    sql = "SELECT line_id,line_name,line_station_id from line"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有线路")
        return 0
    else:
        print("查询线路成功")
        return temp


def getdepartOptions():
    print('return department to choose')

    # 选择一级部门
    sql = "SELECT  department_id,department_name from department where department_id not in " \
          "(select department_id from department where department_id like '%-%')"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有部门")
        return 0
    else:
        print("查询成功")
        return temp
